<?php

// +----------------------------------------------------------------------
// | ThinkAdmin
// +----------------------------------------------------------------------
// | 版权所有 2014~2019 广州楚才信息科技有限公司 [ http://www.cuci.cc ]
// +----------------------------------------------------------------------
// | 官方网站: http://demo.thinkadmin.top
// +----------------------------------------------------------------------
// | 开源协议 ( https://mit-license.org )
// +----------------------------------------------------------------------
// | gitee 代码仓库：https://gitee.com/zoujingli/ThinkAdmin
// | github 代码仓库：https://github.com/zoujingli/ThinkAdmin
// +----------------------------------------------------------------------

namespace app\admin\controller;

use library\Controller;
use library\service\AdminService;
use library\service\MenuService;
use library\tools\Data;
use think\Console;
use think\Db;
use think\exception\HttpResponseException;
/**工具**/
use GatewayWorker\Lib\Gateway;
use redis\Redis;
use app\workerman\Events;
/**
 * 系统公共操作
 * Class Index
 * @package app\admin\controller
 */
class Index extends Controller
{
    protected $table = 'VisitorList';

    /**
     * [test description]
     * @auth         true
     * @throws           hink\Exception
     * @throws           hink\exception\PDOException
     * @HtttpRequest                              get|post
     * @Author       GNLEON
     * @Param
     * @DateTime     2020-11-09T11:24:26+0800
     * @LastTime     2020-11-09T11:24:26+0800
     * @return       [type]                       [description]
     */
    public function test(){
        $dbconf = $this->getconfig('vister_db');
        $redis = new Redis;
        $client_id = $redis::likeget('*');
        // $re = $redis::hset('pctest','uid1',['name'=>1,'sex'=>'2']);
        $res = json_decode($redis::hget('dev_pc_index','7f00000108fc00000010'),true);
        $new_uinfo = Db::connect($dbconf)->table('view_user')->where('id = '.$res['buid'])->find();
        $ress = Db::connect($dbconf)->table('view_company')->where('property_id = '.$new_uinfo['companyid'])->count();
        // $res =  $redis->likeget('*');
        // $re = $redis::hset('')
        var_dump($client_id);
        var_dump($res);
        var_dump($ress);
    }

    /**
     * 显示后台首页
     * @throws \ReflectionException
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function index()
    {
        // $str = '';
        // for ($i=0; $i <30 ; $i++) { 
        //     # code...
        //     $str = $str.'(SELECT count(id) as count,DATE_SUB(CURDATE(), INTERVAL '.$i.' DAY) as date FROM view_new_app_meeting_info_copy where DATE_SUB(CURDATE(), INTERVAL '.$i.' DAY) = FROM_UNIXTIME( app_visittime,"%Y-%m-%d")) as date'.$i.',' ;
        
        // }
        // var_dump($str);

        // exit();
        $this->title = '系统管理后台';
        $auth = AdminService::instance()->apply(true);
        $this->menus = MenuService::instance()->getTree(); 
        if (empty($this->menus) && !$auth->isLogin()) {
            $this->redirect('@admin/login');
        } else {
            
            $this->fetch('index/index');
        }
    }
    /**
     * 首页数据
     * @auth        true
     * @throws      \think\Exception
     * @throws      \think\exception\PDOException
     * @HtttpRequest                          get|post
     * @Author       
     * @Param
     * ecarts (请求接口数据)      week month date
     * @DateTime    
     * @LastTime     2020-08-21T08:44:52+0800
     * @return       [type]                   [description]
     */
    public function main()
    {
        $this->think_ver = \think\App::VERSION;
        $this->mysql_ver = Db::query('select version() as ver')[0]['ver'];
        $dbconf = $this->getconfig('vister_db');

        $uinfo = $this->getGuserinfo();
        $echarts = $this->request->param('echarts');

        if($uinfo['db2']!=NULL && $uinfo['db2']['roleid']!=1){
            /**
             * 
             * code ....该企业数据
             * 
             */
        
        }
        //var_dump($uinfo);
        switch ($uinfo['db1']['authorize']) {
            case 0:
            case '1':
                $historydata =  Db::connect($dbconf)->table('view_sysdata')
                            ->find();
                $where_vistor = 'appointment_status is not null and vister_name is not null';
                //echarts 测试数据 可删
                // $this->totalJson = ['xs' => [], 'ys' => []];
                // for ($i=0; $i < 7; $i++) { 
                //     # code...
                //     $time = strtotime("-{$i} day");
                //     $this->totalJson['xs'][] = date('m月d日', $time);
                //     $this->totalJson['ys']['_0'][] = 23+$i*10;
                //     $this->totalJson['ys']['_1'][] = 50+$i*2;
                // }
                $historydata['echarts'] = $this->indexecharts('week');

                //今日到访人数
                $nowday_appuser_count1 = Db::connect($dbconf)->table('view_new_app_meeting_info_copy')
                                                                     ->where('status in (1,3)')
                                                                     ->where('app_visittime','between',[strtotime(date('Ymd')),strtotime(date('Ymd'))+ 86400])
                                                                     ->count();
                $nowday_appuser_count2 = Db::connect($dbconf)->table('view_new_app_meeting_info_copy')
                                                     ->where('status = 0 and arrive_status != 0')
                                                     ->where('app_visittime','between',[strtotime(date('Ymd')),strtotime(date('Ymd'))+ 86400])
                                                     ->count();
                $historydata['nowday_appuser'] = $nowday_appuser_count1 + $nowday_appuser_count2;
                //今日预约访客数
                $historydata['nowday_readyuser'] = Db::connect($dbconf)->table('view_new_app_meeting_info_copy')
                                                     ->where('vister_uid is not null')
                                                     ->where('app_visittime','between',[strtotime(date('Ymd')),strtotime(date('Ymd'))+ 86400])
                                                     ->count();

                if($echarts){
                    $res= $this->indexecharts($echarts);
                    $this->success('',$res,200);
                }
                break;
                # code...
            case '2':
            case '3':
            case '4':
            case '5':
            case '6':
                $historydata = [];
                $historydata['count_user'] = Db::connect($dbconf)->table('view_user')->where('companyid = '.$uinfo['db2']['companyid'])->count();
                $historydata['count_appointment'] = Db::connect($dbconf)->table('view_appointment')->where('companyid = '.$uinfo['db2']['companyid'])->count();
                $historydata['count_visit'] = Db::connect($dbconf)->table('view_new_app_meeting_info_copy')->where('companyid = '.$uinfo['db2']['companyid'])->count()*2;
                $historydata['nowday_user'] = 0;
                $historydata['yesterday_user'] = 0;
                $historydata['count_visit_user'] = 0;
                
                //今日到访人数
                $nowday_appuser_count1 = Db::connect($dbconf)->table('view_new_app_meeting_info_copy')
                                                                     ->where('companyid = '.$uinfo['db2']['companyid'].' and status in (1,3)')
                                                                     ->where('app_visittime','between',[strtotime(date('Ymd')),strtotime(date('Ymd'))+ 86400])
                                                                     ->count();
                $nowday_appuser_count2 = Db::connect($dbconf)->table('view_new_app_meeting_info_copy')
                                                     ->where('companyid = '.$uinfo['db2']['companyid'].' and status = 0 and arrive_status != 0')
                                                     ->where('app_visittime','between',[strtotime(date('Ymd')),strtotime(date('Ymd'))+ 86400])
                                                     ->count();
                $historydata['nowday_appuser'] = $nowday_appuser_count1 + $nowday_appuser_count2;
                //今日预约访客数
                $historydata['nowday_readyuser'] = Db::connect($dbconf)->table('view_new_app_meeting_info_copy')
                                                     ->where('companyid = '.$uinfo['db2']['companyid'].' and vister_uid is not null')
                                                     ->where('app_visittime','between',[strtotime(date('Ymd')),strtotime(date('Ymd'))+ 86400])
                                                     ->count();

                $where_vistor = 'appointment_status is not null and vister_name is not null and companyid = '.$uinfo['db2']['companyid'];

                $historydata['echartsindex'] = $this->indexecharts('week');

                if($echarts){
                    $res= $this->indexecharts($echarts);
                    $this->success('',$res,200);
                }

                break;
            case '8':
                
                $incompanylist = Db::connect($dbconf)->table('view_property_relation')->where('property_id = '.$uinfo['db2']['companyid'])->select();
                $incompany_id = array_column($incompanylist, "incompany_id");
                $incompany_id = implode($incompany_id, ",");
                $historydata = [];
                $historydata['count_company'] = count($incompanylist);
                $historydata['count_user'] = 0;
                $historydata['count_visit'] = Db::connect($dbconf)->table('view_new_app_meeting_info_copy')->where('companyid in ('.$incompany_id.')')->count()*2;
                $historydata['count_appointment'] = Db::connect($dbconf)->table('view_new_app_meeting_info_copy')->where('companyid in ('.$incompany_id.')')->count();
                $historydata['nowday_user'] = 0;
                $historydata['yesterday_user'] = 0;
                $historydata['count_visit_user'] = Db::connect($dbconf)->table('view_new_app_meeting_info_copy')->where('companyid in ('.$incompany_id.')')->count();

                //今日到访人数
                $nowday_appuser_count1 = Db::connect($dbconf)->table('view_new_app_meeting_info_copy')
                                                             ->where('companyid in ('.$incompany_id.') and status in (1,3)')
                                                             ->where('app_visittime','between',[strtotime(date('Ymd')),strtotime(date('Ymd'))+ 86400])
                                                             ->count();
                $nowday_appuser_count2 = Db::connect($dbconf)->table('view_new_app_meeting_info_copy')
                                                             ->where('companyid in ('.$incompany_id.') and status = 0 and arrive_status != 0')
                                                             ->where('app_visittime','between',[strtotime(date('Ymd')),strtotime(date('Ymd'))+ 86400])
                                                             ->count();
                $historydata['nowday_appuser'] = $nowday_appuser_count1 + $nowday_appuser_count2;
                //今日预约访客数
                $historydata['nowday_readyuser'] = Db::connect($dbconf)->table('view_new_app_meeting_info_copy')
                                                                       ->where('companyid in ('.$incompany_id.') and vister_uid is not null')
                                                                       ->where('app_visittime','between',[strtotime(date('Ymd')),strtotime(date('Ymd'))+ 86400])
                                                                       ->count();

                //$where_vistor = '';
                $where_vistor = 'appointment_status is not null and vister_name is not null and companyid in ('.$incompany_id.')';


                if($echarts){
                    $res= $this->indexecharts($echarts);
                    $this->success('',$res,200);
                }
                break;
            default:
                $this->error('无权查看信息');
                # code...
                break;
        }
        
        $visitorList= Db::connect($dbconf)->table('view_new_app_meeting_info')->where($where_vistor)->order('leavetime desc')->select();

        $this->fetch('main',['historydata'=>$historydata,'list'=>$visitorList]);


      
/*sql remark*/
/**
 SELECT
*
from
(select Count(company.id) AS count_company from company)as company ,
(select Count(`user`.id) AS count_user from `user`) as user ,
(select COUNT(arrive.id)*2 AS count_visit from arrive) as arrive,
(select count(appointment.id) as count_appointment from appointment) as appointment,
(SELECT count(u1.id) as nowday_user from `user` as u1 where u1.createtime BETWEEN unix_timestamp(cast(sysdate() AS date))
  AND unix_timestamp(
   date_format(now(), '%Y-%m-%d 23:59:59')
  ) ) as u1,
(select count(u2.id) as yesterday_user from `user` as u2 where u2.createtime between unix_timestamp(cast(sysdate() AS date)- INTERVAL 1 DAY) and unix_timestamp(
   date_format(now() - interval 1 day, '%Y-%m-%d 23:59:59')
  )) as u2,


(select count(*) as count_visit_user  from (select COUNT(arrive1.id)*2 AS count_visit_user from arrive as arrive1 group by arrive1.uid ) as arrive1) as arrive1,

(select count(*) as count_yesterday_visit_user  from (select COUNT(arrive2.id)*2 AS count_yesterday_visit_user from arrive as arrive2 where createtime  between unix_timestamp(cast(sysdate() AS date)- INTERVAL 1 DAY) and unix_timestamp(
 date_format(now() - interval 1 day, '%Y-%m-%d 23:59:59')
) group by arrive2.uid ) as arrive2) as arrive2,


(select count(*) as count_member from (select count(*) as count_member from commodity_orders as member1 group by member1.userid) as member1) as member1,


(select count(*) as count_yesterday_member from (select count(*) as count_yesterday_member from commodity_orders as member2 where member2.status = 2 and member2.createtime between unix_timestamp(cast(sysdate() AS date)- INTERVAL 1 DAY) and unix_timestamp(
 date_format(now() - interval 1 day, '%Y-%m-%d 23:59:59')
) group by member2.userid) as member2) as member2,


(select sum(members_orders1.price) as  count_income from view_commodity_orders as members_orders1 where payTime is not null and status = 2 )as members_orders1,

(select sum(members_orders2.price) as  count_yesterday_income from view_commodity_orders as members_orders2 where payTime is not null and status = 2 and payTime between unix_timestamp(cast(sysdate() AS date)- INTERVAL 1 DAY) and unix_timestamp(
 date_format(now() - interval 1 day, '%Y-%m-%d 23:59:59')
) )as members_orders2
**/
    }

    /**
     * 修改密码
     * @login true
     * @param integer $id
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function pass($id)
    {   
        $userinfo = $this->getGuserinfo();
        if(!$id || $id == 'null'){
            $id = $userinfo['db1']['id'];
        }

        $this->applyCsrfToken();
        if (intval($id) !== intval(session('user.id'))) {
            $this->error('只能修改当前用户的密码！');
        }
        if (!AdminService::instance()->isLogin()) {
            $this->error('需要登录才能操作哦！');
        }
        if ($this->request->isGet()) {

            $this->verify = true;
            $this->_form('SystemUser', 'admin@user/pass', 'id', [], ['id' => $id]);
        } else {
            $data = $this->_input([
                'password'    => $this->request->post('password'),
                'repassword'  => $this->request->post('repassword'),
                'oldpassword' => $this->request->post('oldpassword'),
            ], [
                'oldpassword' => 'require',
                'password'    => 'require|min:4',
                'repassword'  => 'require|confirm:password',
            ], [
                'oldpassword.require' => '旧密码不能为空！',
                'password.require'    => '登录密码不能为空！',
                'password.min'        => '登录密码长度不能少于4位有效字符！',
                'repassword.require'  => '重复密码不能为空！',
                'repassword.confirm'  => '重复密码与登录密码不匹配，请重新输入！',
            ]);
            $user = Db::name('SystemUser')->where(['id' => $id])->find();
            if (md5($data['oldpassword']) !== $user['password']) {
                $this->error('旧密码验证失败，请重新输入！');
            }
            if (Data::save('SystemUser', ['id' => $user['id'], 'password' => md5($data['password'])])) {
                $this->success('密码修改成功，下次请使用新密码登录！', '');
            } else {
                $this->error('密码修改失败，请稍候再试！');
            }
        }
    }

    /**
     * 修改用户资料
     * @login true
     * @param integer $id 会员ID
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function info($id = 0)
    {
        if (!AdminService::instance()->isLogin()) {
            $this->error('需要登录才能操作哦！');
        }
        $this->applyCsrfToken();
        if (intval($id) === intval(session('user.id'))) {
            $this->_form('SystemUser', 'admin@user/form', 'id', [], ['id' => $id]);
        } else {
            $this->error('只能修改登录用户的资料！');
        }
    }

    /**
     * 清理运行缓存
     * @auth true
     */
    public function clearRuntime()
    {
        try {
            Console::call('clear');
            Console::call('xclean:session');
            $this->success('清理运行缓存成功！');
        } catch (HttpResponseException $exception) {
            throw $exception;
        } catch (\Exception $e) {
            $this->error("清理运行缓存失败，{$e->getMessage()}");
        }
    }

    /**
     * 压缩发布系统
     * @auth true
     */
    public function buildOptimize()
    {
        try {
            Console::call('optimize:route');
            Console::call('optimize:schema');
            Console::call('optimize:autoload');
            Console::call('optimize:config');
            $this->success('压缩发布成功！');
        } catch (HttpResponseException $exception) {
            throw $exception;
        } catch (\Exception $e) {
            $this->error("压缩发布失败，{$e->getMessage()}");
        }
    }

    /**
     * 发送访客微信通知
     * @auth         true
     * @throws           hink\Exception
     * @throws           hink\exception\PDOException
     * @HtttpRequest                              get|post
     * @Author       GNLEON
     * @Param
     * @DateTime     2020-10-29T15:25:57+0800
     * @LastTime     2020-10-29T15:25:57+0800
     * @return       [type]                       [description]
     */
    public function senwx_message(){

        if($this->request->isPost()){
            $this->error('施工中......');
            /**code send user wechat mesage**/
        }
        $this->fetch('sendvisterwx');
    }

    /**
     * 设置来访访客
     * @auth         true
     * @throws           hink\Exception
     * @throws           hink\exception\PDOException
     * @HtttpRequest                              get|post
     * @Author       GNLEON
     * @Param
     * @DateTime     2020-10-29T15:26:35+0800
     * @LastTime     2020-10-29T15:26:35+0800
     * @return       [type]                       [description]
     */
    public function setting_vister(){
        $dbconf = $this->getconfig('vister_db');
        $userinfo = $this->getGuserinfo();
        switch ($userinfo['db1']['authorize']) {
            case '1':
                $where = '';
                # code...
                break;
            case '8':
                $where = 'property_id = '.$userinfo['db2']['companyid'];
                # code...
                break;
            default:
                $this->error('无权限操作');
                # code...
                break;
        }
        $data['companylist'] = Db::connect($dbconf)->table('view_company')->where($where)->select();
        $data['deptlist'] = [];
        if($this->request->isPost()){
            $this->error('施工中......');
            /**code by setting appointment**/


        }
        $this->assign('data',$data);
        $this->fetch('settingvister');
    }

    /**
     * 绑定wss uid
     * @auth         false
     * @throws           hink\Exception
     * @throws           hink\exception\PDOException
     * @HtttpRequest                              get|post
     * @Author       GNLEON
     * @Param
     * @DateTime     2020-10-30T15:12:09+0800
     * @LastTime     2020-10-30T15:12:09+0800
     * @return       [type]                       [description]
     */
    public function bind(){
        $redis = new Redis;
        $userinfo = $this->getGuserinfo();
        $client_id = $this->request->param('client_id');
        $Events = new Events;
        $message = [
            'type'=>'bind',
            'client_id' => $client_id,
            'auid' => $userinfo['db1']['id'],
            'buid' => $userinfo['db2']['id'],
        ];

        //true 以获取一次 false | null 未获取
        $redis::set('wss_client_id'.$client_id.'_repdata','true');
        $has_data = [
            'auid' => @$userinfo['db1']['id']?:'',
            'buid' => @$userinfo['db2']['id']?:'',
            'authorize' => @$userinfo['db1']['authorize']?:'',
        ];
        $res = $redis::hset('dev_pc_index', $client_id, $has_data);

        if($res){
            $this->success('成功绑定');
        }else{
            $this->error('绑定失败');
        }
        
        // $res = Gateway::bindUid($client_id, $userinfo['db1']['id']);
        // $this->success('bind请求成功',$res,200);
    }

    /**
     * 获取echarts历史数据
     * @HtttpRequest                              get|post
     * @Author       GNLEON
     * @Param
     * @DateTime     2020-11-04T16:17:50+0800
     * @LastTime     2020-11-04T16:17:50+0800
     * @param        [type]                       $type    [description]
     * @return       [type]                                [description]
     * @remark       
     */
    public function indexecharts($type){
        $dbconf = $this->getconfig('vister_db');
        $userinfo = $this->getGuserinfo();
        switch ($type) {
            case 'week':
                switch ($userinfo['db1']['authorize']) {
                    case '1':
                        $where = "datelist between '".date('Y-m-d',time()-3600*6*24)."' and '".date('Y-m-d',time())."'";
                        $res = Db::connect($dbconf)->table('view_new_app_meeting_info_copy_history')->where($where)->select();
                        # code...
                        break;
                    case '2':
                        $sql = $this->echartssql($userinfo['db2']['companyid'],[date('Y-m-d',time()-3600*6*24),date('Y-m-d',time())]);
                        $res = Db::connect($dbconf)->query($sql);
                        break;
                    case '8':
                        $company_res =  Db::connect($dbconf)->table('view_company')->field('id')->where(['property_id' => $userinfo['db2']['companyid']])->order('createtime desc')->select();

                        if($company_res){
                            $inid = array_column($company_res, 'id');
                            $inid = implode(",", $inid);
                            $companyid_in = $inid;
                        }else{
                             $companyid_in = 0;
                        }
                        $sql = $this->echartssql($companyid_in,[date('Y-m-d',time()-3600*6*24),date('Y-m-d',time())]);
                        $res = Db::connect($dbconf)->query($sql);
                        # code...
                        break;
                    default:
                        # code...
                        break;
                }

                return $res;
                # code...
                break;
            case 'month':
                switch ($userinfo['db1']['authorize']) {
                    case '1':
                        $where = "datelist between '".date('Y-m-d',time()-3600*30*24)."' and '".date('Y-m-d',time())."'";
                        $res = Db::connect($dbconf)->table('view_new_app_meeting_info_copy_history')->where($where)->select();
                        # code...
                        break;
                    case '2':
                        $sql = $this->echartssql($userinfo['db2']['companyid'],[date('Y-m-d',time()-3600*30*24),date('Y-m-d',time())]);
                        $res = Db::connect($dbconf)->query($sql);
                        break;
                        # code...
                        break;
                    case '8':
                        $company_res =  Db::connect($dbconf)->table('view_company')->field('id')->where(['property_id' => $userinfo['db2']['companyid']])->order('createtime desc')->select();

                        if($company_res){
                            $inid = array_column($company_res, 'id');
                            $inid = implode(",", $inid);
                            $companyid_in = $inid;
                        }else{
                             $companyid_in = 0;
                        }
                        $sql = $this->echartssql($companyid_in,[date('Y-m-d',time()-3600*30*24),date('Y-m-d',time())]);
                        $res = Db::connect($dbconf)->query($sql);
                        break;
                    default:
                        $res = [];
                        # code...
                        break;
                }

                return $res;
                break;
            case 'date':
                $search_time = $this->request->param('echarts_time');
                $search_time = explode(" - ",$search_time);
                switch ($userinfo['db1']['authorize']) {
                    case '1':
                        $where = "datelist between '".$search_time[0]."' and '".$search_time[1]."'";
                        $res = Db::connect($dbconf)->table('view_new_app_meeting_info_copy_history')->where($where)->select();
                        # code...
                        break;
                    case '2':
                        $sql = $this->echartssql($userinfo['db2']['companyid'],[$search_time[0],$search_time[1]]);
                        $res = Db::connect($dbconf)->query($sql);
                        break;
                        # code...
                        break;
                    case '8':
                        $company_res =  Db::connect($dbconf)->table('view_company')->field('id')->where(['property_id' => $userinfo['db2']['companyid']])->order('createtime desc')->select();

                        if($company_res){
                            $inid = array_column($company_res, 'id');
                            $inid = implode(",", $inid);
                            $companyid_in = $inid;
                        }else{
                             $companyid_in = 0;
                        }
                        $sql = $this->echartssql($companyid_in,[$search_time[0],$search_time[1]]);
                        $res = Db::connect($dbconf)->query($sql);
                        break;
                    default:
                        $res = [];
                        # code...
                        break;
                }
                return $res;
                break;
            default:
                # code...
                break;
        }
    }
    
    public function imagelist(){
        $dbconf = $this->getconfig('vister_db');
        $res = Db::connect($dbconf)->table('image_list')->select();
        $this->success('图片list',$res,200);
    }
    /**
     * 图表sql
     * @HtttpRequest                              get|post
     * @Author       GNLEON
     * @Param
     * @DateTime     2020-11-05T08:54:55+0800
     * @LastTime     2020-11-05T08:54:55+0800
     * @param        [type]                       $companyid [description]
     * @param        array                        $time      [description]
     * @return       [type]                                  [description]
     * $time start end -数组内容日期格式
     */
    protected function echartssql($companyid,$time=[]){
        if(!$companyid && count($time)!=2){$this->error('参数错误');}
        if($time){
            $sql =  "select 
                            c2.datelist,
                            companyid,
                            IFNULL(app_count,0) as app_count
                            from 
                            
                            (SELECT
                            view_appointment.companyid,
                            Count(view_appointment.companyid) AS app_count,
                            c1.datelist
                            FROM
                            view_appointment
                            LEFT JOIN calendar AS c1 ON c1.datelist = view_appointment.visittime_new
                            WHERE
                            view_appointment.companyid in (".$companyid.")
                            GROUP BY
                            c1.datelist
                            ) as companydata RIGHT  JOIN calendar as c2 on c2.datelist = companydata.datelist
                            where c2.datelist between '".$time[0]."' and '".$time[1]."'
                            GROUP BY c2.datelist";
        }else{
            $sql =  "select 
                            c2.datelist,
                            companyid,
                            IFNULL(app_count,0) as app_count
                            from 
                            
                            (SELECT
                            view_appointment.companyid,
                            Count(view_appointment.companyid) AS app_count,
                            c1.datelist
                            FROM
                            view_appointment
                            LEFT JOIN calendar AS c1 ON c1.datelist = view_appointment.visittime_new
                            WHERE
                            view_appointment.companyid in (".$companyid.")
                            GROUP BY
                            c1.datelist
                            ) as companydata RIGHT  JOIN calendar as c2 on c2.datelist = companydata.datelist
                            GROUP BY c2.datelist";
        }
        return $sql;
    }
}



/**
 * 
 *备份 删除

SELECT
    date(dday) ddate,
    count(*) - 1 as num
FROM
    (
        SELECT
           *
        FROM
            calendar 
            UNION 
          
            SELECT
     FROM_UNIXTIME(app_visittime,'%Y-%m-%d') as datelist
            FROM
       view_new_app_meeting_info_copy
     
    )a
GROUP BY ddat





SELECT * 
FROM (SELECT DATEDIFF(now(), FROM_UNIXTIME(app_visittime)) AS days_ago, COUNT(id) AS num_texts 
      FROM view_new_app_meeting_info_copy 
      GROUP BY DATE(FROM_UNIXTIME(app_visittime))) AS temp 
WHERE days_ago <= 7



select DATE_FORMAT( vis_app.time, '%Y-%m-%d' ) days,
 count(*) count 
 from
 (

SELECT id,FROM_UNIXTIME( app_visittime,'%Y-%m-%d') as time  FROM view_new_app_meeting_info_copy where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= FROM_UNIXTIME( app_visittime,'%Y-%m-%d') )as vis_app



SELECT
 *,
 DATE_FORMAT( vis_app.app_visittime, '%Y-%m-%d' ) days,
 count(*) count 
FROM
 
( SELECT * FROM view_new_app_meeting_info_copy
 WHERE DATE_SUB( CURDATE( ), INTERVAL 7 DAY ) >= FROM_UNIXTIME( app_visittime) ) as vis_app
 
GROUP BY
 days; 



SELECT id,FROM_UNIXTIME( app_visittime,'%Y-%m-%d') FROM view_new_app_meeting_info_copy where DATE_SUB(CURDATE(), INTERVAL 1 DAY) = FROM_UNIXTIME( app_visittime,'%Y-%m-%d');



select * from view_new_app_meeting_info_copy  where DATE_SUB(CURDATE(), INTERVAL 1 DAY) 



select * from

(SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) FROM view_new_app_meeting_info_copy where DATE_SUB(CURDATE(), INTERVAL 1 DAY) = FROM_UNIXTIME( app_visittime,'%Y-%m-%d')) 
(SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) FROM view_new_app_meeting_info_copy where DATE_SUB(CURDATE(), INTERVAL 2 DAY) = FROM_UNIXTIME( app_visittime,'%Y-%m-%d')) 
(SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) FROM view_new_app_meeting_info_copy where DATE_SUB(CURDATE(), INTERVAL 3 DAY) = FROM_UNIXTIME( app_visittime,'%Y-%m-%d')) 
(SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) FROM view_new_app_meeting_info_copy where DATE_SUB(CURDATE(), INTERVAL 4 DAY) = FROM_UNIXTIME( app_visittime,'%Y-%m-%d')) 
(SELECT DATE_S
 *
 * 
 */